import psycopg2
from gestion.fondos.models import ItemCheque



def update_1(conn):
  cur = conn.cursor()
  cur.execute("""ALTER TABLE compras_itemcompra ADD COLUMN moneda_id integer""")

def update_2(conn):
  cur = conn.cursor()

  cur.execute("""INSERT INTO cuenta_corriente_pago(formadepago_ptr_id,fecha,hora,total,saldo) SELECT formadepago_ptr_id, fecha,'00:00:00' as hora ,importe AS total, 0 as saldo FROM caja_gastosvarios WHERE formadepago_ptr_id not in (SELECT formadepago_ptr_id FROM cuenta_corriente_pago)""")
  cur.execute("""INSERT INTO cuenta_corriente_pago(formadepago_ptr_id,fecha,hora,total,saldo) SELECT formadepago_ptr_id, fecha,'00:00:00' as hora ,importe AS total, 0 as saldo FROM caja_ingresosvarios WHERE formadepago_ptr_id not in (SELECT formadepago_ptr_id FROM cuenta_corriente_pago)""")
  cur.execute("""ALTER TABLE caja_gastosvarios DROP CONSTRAINT caja_gastosvarios_formadepago_ptr_id_fkey""")
  cur.execute("""ALTER TABLE caja_ingresosvarios DROP CONSTRAINT caja_ingresosvarios_formadepago_ptr_id_fkey""")
  cur.execute("""ALTER TABLE caja_gastosvarios RENAME COLUMN formadepago_ptr_id TO pago_ptr_id""")
  cur.execute("""ALTER TABLE caja_ingresosvarios RENAME COLUMN formadepago_ptr_id TO pago_ptr_id""")
  cur.execute("""ALTER TABLE caja_gastosvarios ADD CONSTRAINT caja_gastosvarios_pago_ptr_id_fkey FOREIGN KEY (pago_ptr_id) REFERENCES cuenta_corriente_pago (formadepago_ptr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED""")
  cur.execute("""ALTER TABLE caja_ingresosvarios ADD CONSTRAINT caja_ingresosvarios_pago_ptr_id_fkey FOREIGN KEY (pago_ptr_id)REFERENCES cuenta_corriente_pago (formadepago_ptr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;""")
      
def update_3(conn):
  cur = conn.cursor()
  cur.execute("""ALTER TABLE caja_gastosvarios ADD COLUMN total character varying(7)""")
  cur.execute("""ALTER TABLE caja_ingresosvarios ADD COLUMN total character varying(7)""")
  
def update_4(conn):
  cur = conn.cursor()
  cur.execute("""ALTER TABLE caja_gastosvarios DROP COLUMN fecha""")
  cur.execute("""ALTER TABLE caja_gastosvarios DROP COLUMN hora""")
  cur.execute("""ALTER TABLE caja_ingresosvarios DROP COLUMN fecha""")
  cur.execute("""ALTER TABLE caja_ingresosvarios DROP COLUMN hora""")
  
def update_5(conn):
  cur = conn.cursor()
  cur.execute(""" CREATE TABLE compras_recibo_de_pago
  (
  recibo_ptr_id integer NOT NULL,
  contacto_id integer NOT NULL,
  CONSTRAINT compras_recibo_de_pago_pkey PRIMARY KEY (recibo_ptr_id),
  CONSTRAINT compras_recibo_de_pago_contacto_id_fkey FOREIGN KEY (contacto_id)
      REFERENCES compras_proveedor (contacto_ptr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT compras_recibo_de_pago_recibo_ptr_id_fkey FOREIGN KEY (recibo_ptr_id)
      REFERENCES cuenta_corriente_recibo (pago_ptr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
  ) """)
  cur.execute(""" CREATE TABLE ventas_recibo_de_cobro
(
  recibo_ptr_id integer NOT NULL,
  contacto_id integer NOT NULL,
  CONSTRAINT ventas_recibo_de_cobro_pkey PRIMARY KEY (recibo_ptr_id),
  CONSTRAINT ventas_recibo_de_cobro_contacto_id_fkey FOREIGN KEY (contacto_id)
      REFERENCES ventas_cliente (contacto_ptr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT ventas_recibo_de_cobro_recibo_ptr_id_fkey FOREIGN KEY (recibo_ptr_id)
      REFERENCES cuenta_corriente_recibo (pago_ptr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
) """)
  cur.execute("""ALTER TABLE cuenta_corriente_pago ADD COLUMN es_ingreso boolean """)
  
  cur.execute("""INSERT INTO ventas_recibo_de_cobro(recibo_ptr_id,contacto_id) SELECT pago_ptr_id as recibo_ptr_id ,contacto_id FROM cuenta_corriente_recibo""")

def update_6(conn):
  cur = conn.cursor()
  cur.execute("""ALTER TABLE fondos_itemcheque ADD COLUMN de_id integer""")
  cur.execute("""ALTER TABLE fondos_itemcheque ADD COLUMN para_id integer""")
  cur.execute("""ALTER TABLE fondos_itemcheque ADD COLUMN fecha_ingreso date""")
  cur.execute("""ALTER TABLE fondos_itemcheque ADD COLUMN fecha_egreso date""")
  cur.execute("""ALTER TABLE fondos_itemcheque ADD CONSTRAINT fondos_itemcheque_contacto_de_id_fkey FOREIGN KEY (de_id)REFERENCES contactos_contacto (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;""")
  cur.execute("""ALTER TABLE fondos_itemcheque ADD CONSTRAINT fondos_itemcheque_contacto_para_id_fkey FOREIGN KEY (para_id)REFERENCES contactos_contacto (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;""")
      
def update_7(conn):
  cur = conn.cursor()
  cur.execute("""ALTER TABLE fondos_formadepago ADD COLUMN tipo character varying(20)""")
  
def update_8(conn):
  cur = conn.cursor()
  cur.execute("""UPDATE cuenta_corriente_pago set es_ingreso=False WHERE formadepago_ptr_id in (select pago_ptr_id from compras_compra) or formadepago_ptr_id in (select recibo_ptr_id from compras_recibo_de_pago) or formadepago_ptr_id in (select pago_ptr_id from caja_gastosvarios)""")
  cur.execute("""UPDATE cuenta_corriente_pago set es_ingreso=True WHERE formadepago_ptr_id in (select pago_ptr_id from ventas_venta) or formadepago_ptr_id in (select recibo_ptr_id from ventas_recibo_de_cobro) or formadepago_ptr_id in (select pago_ptr_id from caja_ingresosvarios)""")
  
def update_9(conn):
  cur = conn.cursor()
  cur.execute("""UPDATE fondos_formadepago SET tipo='GastosVarios' WHERE id in (select pago_ptr_id from caja_gastosvarios) """)
  cur.execute("""UPDATE fondos_formadepago SET tipo='IngresosVarios' WHERE id in (select pago_ptr_id from caja_ingresosvarios) """)
  cur.execute("""UPDATE fondos_formadepago SET tipo='Venta' WHERE id in (select pago_ptr_id from ventas_venta) """)
  cur.execute("""UPDATE fondos_formadepago SET tipo='Compra' WHERE id in (select pago_ptr_id from compras_compra) """)
  cur.execute("""UPDATE fondos_formadepago SET tipo='Recibo_de_cobro' WHERE id in (select recibo_ptr_id from ventas_recibo_de_cobro) """)
  cur.execute("""UPDATE fondos_formadepago SET tipo='Recibo_de_pago' WHERE id in (select recibo_ptr_id from compras_recibo_de_pago) """)
  
def update_10(conn):
  for chek in ItemCheque.objects.all():
    if chek.a_pagar.pago.es_ingreso:
      if chek.a_pagar.tipo == "Venta":
        chek.de = chek.a_pagar.pago.venta.cliente
      if chek.a_pagar.tipo == "IngresosVarios":
        chek.de = chek.a_pagar.pago.ingresosvarios.contacto
      if chek.a_pagar.tipo == "Recibo_de_cobro":
        chek.de = chek.a_pagar.pago.recibo.recibo_de_cobro.contacto
      chek.fecha_ingreso = chek.a_pagar.pago.fecha
    if not chek.a_pagar.pago.es_ingreso:
      if chek.a_pagar.tipo == "Compra":
        chek.para = chek.a_pagar.pago.compra.proveedor
      if chek.a_pagar.tipo == "GastosVarios":
        chek.para = chek.a_pagar.pago.gastosvarios.contacto
      if chek.a_pagar.tipo == "Recibo_de_pago":
        chek.para = chek.a_pagar.pago.recibo.recibo_de_pago.contacto
      chek.fecha_egreso = chek.a_pagar.pago.fecha
    chek.save()
    
    
def update_11(conn):
  cur = conn.cursor()
  cur.execute("""CREATE TABLE cuenta_corriente_itemretencion
(
  id serial NOT NULL,
  concepto_retencion character varying(30) NOT NULL,
  recibo_id integer NOT NULL,
  importe double precision NOT NULL,
  CONSTRAINT cuenta_corriente_itemretencion_pkey PRIMARY KEY (id),
  CONSTRAINT cuenta_corriente_itemretencion_recibo_id_fkey FOREIGN KEY (recibo_id)
      REFERENCES cuenta_corriente_recibo (pago_ptr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)""")

def update_12(conn):
  cur = conn.cursor()
  cur.execute("""ALTER TABLE caja_gastosvarios ADD COLUMN condicion_de_venta character varying(1)""")
  cur.execute("""ALTER TABLE caja_ingresosvarios ADD COLUMN condicion_de_venta character varying(1)""")
  
def update_13(conn):
  cur = conn.cursor()
  cur.execute("""ALTER TABLE cuenta_corriente_recibo ADD COLUMN pagado character varying(10)""")
  #cur.execute("""UPDATE cuenta_corriente_recibo as c, fondos_forma_de_pago as p SET c.pagado=p.pagado WHERE c.id=p.id """)

def update_14(conn):
  cur = conn.cursor()
  cur.execute("""UPDATE cuenta_corriente_recibo  SET pagado=(select p.pagado FROM fondos_formadepago as p WHERE p.id=pago_ptr_id) """)

def update_15(conn):
  cur = conn.cursor()
  cur.execute("""ALTER TABLE ventas_venta ADD COLUMN tipo_comprobante character varying(1)""")
  cur.execute("""UPDATE ventas_venta SET tipo_comprobante = 'F'""")
  cur.execute("""ALTER TABLE compras_compra ADD COLUMN tipo_comprobante character varying(1)""")
  cur.execute("""UPDATE compras_compra SET tipo_comprobante = 'F'""")
